import type { Project } from '../entity'
import type { PageResult } from '@renderer/common/domain'
import { v4 as uuidv4 } from 'uuid'

interface ProjectQuery {
  page: number
  pageSize: number
  name?: string
}

// 定义数据库查询结果的接口
interface ProjectQueryResult {
  id: string
  name: string
  path: string
  description: string
  create_time: string
  update_time: string
}

/**
 * 分页查询项目列表
 * @param query 查询参数，支持按name模糊查询
 */
export async function getByPage(query: ProjectQuery): Promise<PageResult<Project>> {
  const { page, pageSize, name } = query
  const offset = (page - 1) * pageSize

  const whereClause = name ? 'WHERE name LIKE ?' : ''
  const searchParams = name ? [`%${name}%`] : []

  // 获取总数
  const countSql = `SELECT COUNT(*) as total FROM projects ${whereClause}`
  const result = await window.db.get<{ total: number }>(countSql, searchParams)
  const total = result.total

  // 获取列表数据
  const listSql = `
    SELECT * FROM projects
    ${whereClause}
    ORDER BY update_time DESC
    LIMIT ? OFFSET ?
  `
  const list = await window.db.all<ProjectQueryResult>(listSql, [...searchParams, pageSize, offset])

  // 处理数据
  const projects: Project[] = list.map(item => ({
    id: item.id,
    name: item.name,
    path: item.path,
    description: item.description,
    createTime: item.create_time,
    updateTime: item.update_time
  }))

  return {
    list: projects,
    total,
    page,
    pageSize
  }
}

/**
 * 创建项目
 */
export async function add(project: Omit<Project, 'id' | 'createTime' | 'updateTime'>): Promise<string> {
  const now = new Date().toISOString()
  const id = uuidv4()

  await window.db.run(
    `INSERT INTO projects (id, name, path, description, create_time, update_time)
     VALUES (?, ?, ?, ?, ?, ?)`,
    [id, project.name, project.path, project.description, now, now]
  )

  return id
}

/**
 * 更新项目
 */
export async function update(project: Project): Promise<void> {
  const now = new Date().toISOString()

  await window.db.run(
    `UPDATE projects
     SET name = ?, path = ?, description = ?, update_time = ?
     WHERE id = ?`,
    [project.name, project.path, project.description, now, project.id]
  )
}

/**
 * 删除项目
 */
export async function remove(id: string): Promise<void> {
  await window.db.run('DELETE FROM projects WHERE id = ?', [id])
}

/**
 * 获取项目详情
 */
export async function getById(id: string): Promise<Project | null> {
  const result = await window.db.get<ProjectQueryResult>(
    'SELECT * FROM projects WHERE id = ?',
    [id]
  )

  if (!result) return null

  return {
    id: result.id,
    name: result.name,
    path: result.path,
    description: result.description,
    createTime: result.create_time,
    updateTime: result.update_time
  }
}

/**
 * 检查项目名称是否已存在
 */
export async function checkNameExists(name: string, excludeId?: string): Promise<boolean> {
  const sql = excludeId
    ? 'SELECT COUNT(*) as count FROM projects WHERE name = ? AND id != ?'
    : 'SELECT COUNT(*) as count FROM projects WHERE name = ?'
  const params = excludeId ? [name, excludeId] : [name]

  const result = await window.db.get<{ count: number }>(sql, params)
  return result.count > 0
}
